package dao.impl;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import dao.CodePostalDao;
import dao.dto.CodePostalDto;
import dao.dto.ZoneDto;
import boerrors.CodePostalInexistantException;

public class CodePostalDaoImpl extends CodePostalDao {

        private static CodePostalDao _instance = null;
        private CallableStatement _callableStatement = null;
        private SQLConnection _connection = new SQLConnection();

        //Constructeur du singleton
        public static CodePostalDao getInstance(){
                if(_instance == null) _instance = new CodePostalDaoImpl();
                return _instance;
        }

        //Methodes   
        @Override
        public Vector<CodePostalDto> lister() throws CodePostalInexistantException {
                ResultSet resultat = null;
                ZoneDto zone = null;
                CodePostalDto codePostal = null;
                try {                                      
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_CodePostalList}");
                        //execute
                        resultat = _callableStatement.executeQuery();
                        Vector<CodePostalDto> liste = new Vector<CodePostalDto>();
                        while (resultat.next()) {
                                zone = new ZoneDto(resultat.getInt("idZone"),resultat.getString("libZone"));
                                codePostal = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                                resultat.getString("cp"),
                                                                resultat.getString("ville"),
                                                                resultat.getString("libPays"),
                                                                zone);
                                liste.add(codePostal);
                        }
                        return liste;
                } catch(SQLException e) { 
                    throw new CodePostalInexistantException(e);
                } finally { 
                    if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new CodePostalInexistantException(e);}
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new CodePostalInexistantException(e);}
                    _connection.deconnecter();
                }
        }
        
        //Methodes   
        @Override
        public Vector<CodePostalDto> rechercher(String codePostal,String ville) throws CodePostalInexistantException{
                ResultSet resultat = null;
                ZoneDto zone = null;
                CodePostalDto tamponCP = null;
                try {                                      
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_CodePostalSearchBy(?,?)}");
                        //parameter
                        _callableStatement.setString("cp",codePostal);
                        _callableStatement.setString("ville",ville);
                        //execute
                        resultat = _callableStatement.executeQuery();
                        Vector<CodePostalDto> liste = new Vector<CodePostalDto>();
                        while (resultat.next()) {
                                zone = new ZoneDto(resultat.getInt("idZone"),resultat.getString("libZone"));
                                tamponCP = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                                resultat.getString("cp"),
                                                                resultat.getString("ville"),
                                                                resultat.getString("libPays"),
                                                                zone);
                                liste.add(tamponCP);
                        }
                        return liste;
                } catch(SQLException e) { 
                    throw new CodePostalInexistantException(e);
                } finally { 
                    if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new CodePostalInexistantException(e);}
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new CodePostalInexistantException(e);}
                    _connection.deconnecter();
                }
        }
        
}